﻿using ProjectScheduling.models;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ProjectScheduling.daos
{
    public class PreparedStaffDao : BaseDao<PreparedStaff>
    {
        public readonly static PreparedStaffDao Base = new();
        public List<PreparedStaff> FindStaffList(DateTime start, DateTime end, int staffId)
        {
            return Db.Queryable<PreparedStaff>().InnerJoin<PreparedData>((a, b) => a.PreparedId == b.Id).Where((a, b) => b.FromDate > start && b.FromDate < end && a.StaffId == staffId).Select<PreparedStaff>().ToList();
        }
        public DataTable GroupToTable(DateTime start, DateTime end, int type, out DataTable dt)
        {
            var ppIds = new List<int>();
            if (type > 0)
            {
                var types = new List<int> { type };
                //if (type == 1) types.Add(15);
                ppIds = Db.Queryable<Projects>().Where(it => types.Contains(it.ProjectTypeId)).Select(it => it.Id).ToList();
            }
            var dt1 = Db.Queryable<PreparedData>().InnerJoin<PreparedStaff>((a, b) => a.Id == b.PreparedId).Where((a, b) => (type == 0 || ppIds.Contains(a.ProjectId)) && a.FromDate > start && a.FromDate <= end).Select((a, b) => new { a.ProjectId, b.StaffId, b.Days }).ToList();
            var staffs = Db.Queryable<Staff>().Select(it => new { it.Id, it.TrueName }).ToList();
            var pids = dt1.Select(z => z.ProjectId).Distinct().ToList();
            var projects = Db.Queryable<Projects>().Where(it => pids.Contains(it.Id)).Select(it => new { it.Id, it.Name }).ToList();
            dt = new DataTable();
            dt.Columns.Add("ProjectName");
            dt.Columns.Add("Days");
            dt.Columns.Add("MeDays");
            dt.Columns.Add("ProjectId");
            dt.Columns.Add("StaffId");
            dt.Columns.Add("StaffName");
            foreach (var d in dt1.GroupBy(z => new { z.StaffId, z.ProjectId }).ToList())
            {
                var p = projects.Find(z => z.Id == d.Key.ProjectId);
                var staff = staffs.Find(z => z.Id == d.Key.StaffId);
                var row = dt.NewRow();
                row["ProjectName"] = p?.Name ?? "";
                row["Days"] = dt1.Where(z => z.ProjectId == d.Key.ProjectId).Sum(z => z.Days);
                row["MeDays"] = dt1.Where(z => z.ProjectId == d.Key.ProjectId && z.StaffId == d.Key.StaffId).Sum(z => z.Days);
                row["StaffId"] = d.Key.StaffId;
                row["StaffName"] = staff?.TrueName ?? "";
                dt.Rows.Add(row);
            }
            var table = new DataTable();
            new List<string> { "StaffName", "StaffId", "Days", "ProjectCount", "AvagDays" }.ForEach(it => table.Columns.Add(it));
            var lst = new List<(string StaffName, int StaffId, int Days, int ProjectCount, decimal AvagDays)>();
            foreach (var staff in staffs)
            {
                var projectIds = dt1.Where(z => z.StaffId == staff.Id).Select(it => it.ProjectId).ToList();
                var days = dt1.Where(z => projectIds.Contains(z.ProjectId)).Sum(z => z.Days);
                var projectCount = dt1.Where(z => z.StaffId == staff.Id).GroupBy(z => z.ProjectId).Count();
                lst.Add((staff.TrueName ?? "", staff.Id, days, projectCount, projectCount == 0 ? 0 : Math.Round(1m * days / projectCount,2)));
            }
            lst.OrderBy(it => it.AvagDays).ToList().ForEach(item =>
            {
                var row = table.NewRow();
                row["StaffName"] = item.StaffName;
                row["StaffId"] = item.StaffId;
                row["Days"] = item.Days;
                row["ProjectCount"] = item.ProjectCount;
                row["AvagDays"] = item.AvagDays;
                table.Rows.Add(row);
            });

            return table;

        }
    }
}
